package com.cyjf.dbview.mapper;


import com.cyjf.dbview.bo.MarketBO;
import com.cyjf.dbview.bo.MarketDetailCountBO;
import com.cyjf.dbview.bo.ProvinceBO;
import com.cyjf.dbview.model.ZscarProvinces;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;

public interface ZscarProvincesMapper extends Mapper<ZscarProvinces> {

    @Select("SELECT aa.provinces_name as name,SUM(aa.bazaarNumber) as \"bazaarNumber\",SUM(aa.dealerNumber) as\"dealerSum\" \n" +
            "FROM (\n" +
            "SELECT\n" +
            "\t(\n" +
            "\t\tCASE\n" +
            "\t\tWHEN a.dealer_provinces ='25' THEN \"云南省\"\n" +
            "\t\tWHEN a.dealer_provinces ='24' THEN \"贵州省\"\n" +
            "\t\tWHEN a.dealer_provinces ='23' THEN \"四川省\"\n" +
            "\t\tWHEN a.dealer_provinces ='22' THEN \"重庆市\"\n" +
            "\t\tELSE\n" +
            "\t\t\t\"其他\"\n" +
            "\t\tEND\n" +
            "\t) AS \"provinces_name\" ,\n" +
            "\tCOUNT(DISTINCT dealer_id) AS \"dealerNumber\",\n" +
            "\tCOUNT(DISTINCT bazaar_id) AS \"bazaarNumber\"\n" +
            "FROM\n" +
            "\tzscartm1jucom2.zscar_dealer a\n" +
            "LEFT JOIN zscartm1jucom2.zscar_provinces b ON a.dealer_provinces = b.provinces_id where a.dealer_name is not null\n" +
            "GROUP BY a.dealer_provinces ) aa \n" +
            "GROUP BY aa.provinces_name")
    List<ProvinceBO> getProvincesCount();

    //【整车交易】市场详情
    @Select("SELECT * from \n" +
            "(SELECT\n" +
            " b.provinces_name,\n" +
            " f.bazzar_title,\n" +
            " COUNT(DISTINCT a.dealer_id) AS \"dealerNumber\",\n" +
            " COUNT(DISTINCT ee.dealer_id2) as 'activedealers',\n" +
            " ROUND(SUM(ee.amount2)/10000,2) AS \"monthamount\",\n" +
            " ROUND(SUM(dd.amount1)/10000,2) AS \"totleamount\",\n" +
            "\n" +
            "SUM(ee.amount2)/ \n" +
            "(SELECT\n" +
            "  SUM(a.amount)\n" +
            " FROM\n" +
            "  zscartm1jucom2.zscar_sales_order_paymentdocument a\n" +
            " WHERE\n" +
            "  a.deal_status = 2 AND a.pay_status = 1 AND FROM_UNIXTIME(a.create_time, '%Y/%m') = DATE_FORMAT(NOW(), '%Y/%m'))  as \"monthamount_Proportion\",\n" +
            "\n" +
            "SUM(dd.amount1)/(SELECT\n" +
            "  SUM(a.amount)\n" +
            " FROM\n" +
            "  zscartm1jucom2.zscar_sales_order_paymentdocument a\n" +
            " WHERE\n" +
            "  a.deal_status = 2 AND a.pay_status = 1 AND FROM_UNIXTIME(a.create_time, '%Y') = DATE_FORMAT(NOW(),'%Y') ) as 'totalamount_Proportion',\n" +
            "\t\n" +
            "COUNT(DISTINCT a.dealer_id) /\n" +
            "(SELECT COUNT(DISTINCT dealer_id) from zscartm1jucom2.zscar_dealer WHERE  dealer_name!='' ) as 'dealer_Proportion',\n" +
            "\n" +
            " COUNT(DISTINCT ee.dealer_id2) /\n" +
            "(SELECT COUNT(DISTINCT dealer_id) from zscartm1jucom2.zscar_dealer WHERE  dealer_name!='' ) as 'activedealer_Proportion'\n" +
            "\n" +
            "FROM\n" +
            " zscartm1jucom2.zscar_dealer a\n" +
            "LEFT JOIN zscartm1jucom2.zscar_provinces b ON a.dealer_provinces = b.provinces_id\n" +
            "LEFT JOIN (\n" +
            " SELECT\n" +
            "  c.dealer_id AS \"dealer_id1\",\n" +
            "  SUM(a.amount) AS \"amount1\"\n" +
            " FROM\n" +
            "  zscartm1jucom2.zscar_sales_order_paymentdocument a\n" +
            " LEFT JOIN zscartm1jucom2.zscar_sales_order b ON a.sales_no = b.sales_no\n" +
            " LEFT JOIN zscartm1jucom2.zscar_dealer c ON b.dealer_id = c.dealer_id\n" +
            " WHERE\n" +
            "  a.deal_status = 2\n" +
            " AND a.pay_status = 1\n" +
            " AND FROM_UNIXTIME(a.create_time, '%Y') = DATE_FORMAT(NOW(),'%Y')\n" +
            " GROUP BY\n" +
            "  c.dealer_id\n" +
            ") dd ON a.dealer_id = dd.dealer_id1\n" +
            "LEFT JOIN (\n" +
            " SELECT\n" +
            "  c.dealer_id AS \"dealer_id2\",\n" +
            "  SUM(a.amount) AS \"amount2\"\n" +
            " FROM\n" +
            "  zscartm1jucom2.zscar_sales_order_paymentdocument a\n" +
            " LEFT JOIN zscartm1jucom2.zscar_sales_order b ON a.sales_no = b.sales_no\n" +
            " LEFT JOIN zscartm1jucom2.zscar_dealer c ON b.dealer_id = c.dealer_id\n" +
            " WHERE\n" +
            "  a.deal_status = 2\n" +
            " AND a.pay_status = 1\n" +
            " AND FROM_UNIXTIME(a.create_time, '%Y/%m') = DATE_FORMAT(NOW(), '%Y/%m')\n" +
            " GROUP BY\n" +
            "  c.dealer_id\n" +
            ") ee ON a.dealer_id = ee.dealer_id2\n" +
            "LEFT JOIN zscartm1jucom2.zscar_bazaar f on a.bazaar_id=f.bazaar_id\n" +
            "WHERE  a.dealer_name!=' ' \n" +
            "GROUP BY\n" +
            " b.provinces_name,\n" +
            "f.bazzar_title) aaa  WHERE aaa.provinces_name like concat('%', #{prov}, '%') ")
    List<MarketBO> getMarketDetail(@Param("prov") String prov);

    //市场详情总计栏（按照筛选项的省份总计，不选为全部总计）
    @Select("SELECT\n" +
            " COUNT(DISTINCT a.dealer_id) AS \"dealerNumber\",\n" +
            " COUNT(DISTINCT ee.dealer_id2) as 'activedealers',\n" +
            " ROUND(SUM(ee.amount2)/10000,2) AS \"monthamount\",\n" +
            " ROUND(SUM(dd.amount1)/10000,2) AS \"totalamount\"\n" +
            "FROM\n" +
            " zscartm1jucom2.zscar_dealer a\n" +
            "LEFT JOIN zscartm1jucom2.zscar_provinces b ON a.dealer_provinces = b.provinces_id\n" +
            "LEFT JOIN (\n" +
            " SELECT\n" +
            "  c.dealer_id AS \"dealer_id1\",\n" +
            "  SUM(a.amount) AS \"amount1\"\n" +
            " FROM\n" +
            "  zscartm1jucom2.zscar_sales_order_paymentdocument a\n" +
            " LEFT JOIN zscartm1jucom2.zscar_sales_order b ON a.sales_no = b.sales_no\n" +
            " LEFT JOIN zscartm1jucom2.zscar_dealer c ON b.dealer_id = c.dealer_id\n" +
            " WHERE\n" +
            "  a.deal_status = 2\n" +
            " AND a.pay_status = 1\n" +
            " AND FROM_UNIXTIME(a.create_time, '%Y') = DATE_FORMAT(NOW(),'%Y')\n" +
            " GROUP BY\n" +
            "  c.dealer_id\n" +
            ") dd ON a.dealer_id = dd.dealer_id1\n" +
            "LEFT JOIN (\n" +
            " SELECT\n" +
            "  c.dealer_id AS \"dealer_id2\",\n" +
            "  SUM(a.amount) AS \"amount2\"\n" +
            " FROM\n" +
            "  zscartm1jucom2.zscar_sales_order_paymentdocument a\n" +
            " LEFT JOIN zscartm1jucom2.zscar_sales_order b ON a.sales_no = b.sales_no\n" +
            " LEFT JOIN zscartm1jucom2.zscar_dealer c ON b.dealer_id = c.dealer_id\n" +
            " WHERE\n" +
            "  a.deal_status = 2\n" +
            " AND a.pay_status = 1\n" +
            " AND FROM_UNIXTIME(a.create_time, '%Y/%m') = DATE_FORMAT(NOW(), '%Y/%m')\n" +
            " GROUP BY\n" +
            "  c.dealer_id\n" +
            ") ee ON a.dealer_id = ee.dealer_id2\n" +
            "LEFT JOIN zscartm1jucom2.zscar_bazaar f on a.bazaar_id=f.bazaar_id\n" +
            "WHERE  a.dealer_name !='' \n" +
            "and b.provinces_name like '%重庆%'")
    MarketDetailCountBO getMarketDetailCount();

}